package vn.edu.saigontech.ncms.web.receipt;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;

import vn.edu.saigontech.ncms.model.info.Receipt;
import vn.edu.saigontech.ncms.model.info.ReceiptMaintenance;
import vn.edu.saigontech.ncms.service.ReceiptMaintenanceService;
import vn.edu.saigontech.ncms.service.ReceiptService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;
import vn.edu.saigontech.ncms.web.receipt.report.ReceiptMaintenanceMonthlyBean;

@SuppressWarnings("serial")
public class ReceiptMaintenanceAction extends BaseAction {
	
	private Long id;
	private Long receiptId;
	private String number;
	private String date;
	private Double fee;
	private String reason;
	private int month;
	private int year;
	
	private String fromDate;
	private String toDate;
	
	public String list() {
		REQUEST().setAttribute("maintenances", ReceiptMaintenanceService.INSTANCE.findAll());
		return SUCCESS;
	}
	
	public String showAdd() {
		REQUEST().setAttribute("receipt", ReceiptService.INSTANCE.findById(this.receiptId));
		return SUCCESS;
	}
	
	public String add() {
		ReceiptMaintenance maintenance = new ReceiptMaintenance();
		Receipt receipt = ReceiptService.INSTANCE.findById(this.receiptId);
		maintenance.setReceipt(receipt);
		maintenance.setNumber(this.number);
		maintenance.setDate(DateUtil.fromString(this.date, this.i18n("format.date")));
		maintenance.setFee(this.fee);
		maintenance.setReason(this.reason);
		ReceiptMaintenanceService.INSTANCE.persist(maintenance);
		this.id = maintenance.getId();
		
		String student = receipt.getStudent().getFullName();
		String number = receipt.getNumber();
		String offering = receipt.getOffering().getName();
		
		this.addLoggingDetail(BaseAction.ADD, "logging.maintenance.add", student, number, offering);
		return SUCCESS;
	}
	
	public String showEdit() {
		REQUEST().setAttribute("maintenance", ReceiptMaintenanceService.INSTANCE.findById(this.id));
		return SUCCESS;
	}
	
	public String edit() {
		ReceiptMaintenance maintenance = ReceiptMaintenanceService.INSTANCE.findById(this.id);
		maintenance.setDate(DateUtil.fromString(this.date, this.i18n("format.date")));
		maintenance.setNumber(this.number);
		maintenance.setFee(this.fee);
		maintenance.setReason(this.reason);
		ReceiptMaintenanceService.INSTANCE.persist(maintenance);
		
		String student = maintenance.getReceipt().getStudent().getFullName();
		String number = maintenance.getReceipt().getNumber();
		String offering = maintenance.getReceipt().getOffering().getName();
		
		this.addLoggingDetail(BaseAction.EDIT, "logging.maintenance.edit", student, number, offering);
		return SUCCESS;
	}
	
	public String delete() {
		ReceiptMaintenance maintenance = ReceiptMaintenanceService.INSTANCE.findById(this.id);
		String student = maintenance.getReceipt().getStudent().getFullName();
		String number = maintenance.getReceipt().getNumber();
		String offering = maintenance.getReceipt().getOffering().getName();
		
		if(ReceiptMaintenanceService.INSTANCE.deleteById(this.id)) {
			this.setKeyMessage("confirm.deleteSuccessfully", "maintenance");
			this.addLoggingDetail(BaseAction.DELETE, "logging.maintenance.delete", student, number, offering);
		}
		else {
			this.setKeyMessage("confirm.deleteUnsuccessfully", "maintenance");
		}		
		return SUCCESS;
	}
	
	public String showReport() {
		REQUEST().setAttribute("fromDate", DateUtil.toString(DateUtil.startOfMonth(DateUtil.newDate()), this.i18n("format.date")));
		REQUEST().setAttribute("toDate", DateUtil.toString(DateUtil.endOfMonth(DateUtil.newDate()), this.i18n("format.date")));
		return SUCCESS;
	}
	
	public String report() {
		Date from = DateUtil.fromString(this.fromDate, this.i18n("format.date"));
		Date to = DateUtil.fromString(this.toDate, this.i18n("format.date"));
		
		List<Object[]> obj = ReceiptMaintenanceService.INSTANCE.findByDates(from, to);
		List<ReceiptMaintenanceMonthlyBean> details = new ArrayList<ReceiptMaintenanceMonthlyBean>();
		for (Object[] each : obj) {
			ReceiptMaintenanceMonthlyBean bean = new ReceiptMaintenanceMonthlyBean(each);
			details.add(bean);
		}
		if (details.size() <= 0) return SUCCESS;
		HSSFWorkbook wb = ExcelUtil.getTemplate("receipt-maintenance-monthly.xls");
			
		Map<String, CellStyle> styles = createStyles(wb);
		if (Checker.isNull(wb)) return NONE;

		/* BEGIN WRITING DATA */
		HSSFSheet sheet = wb.getSheetAt(0);
		
		HSSFRow row = sheet.getRow(1);
		HSSFCell cell = row.getCell(0);
		cell.setCellValue("Từ ngày " + DateUtil.toString(from, "dd/MM/yyyy") + " đến ngày " + DateUtil.toString(to, "dd/MM/yyyy"));
		
		int cRow = 5;
		int lastRow = generateCells(cRow, details, sheet, styles, 0);
		
		row = sheet.createRow(lastRow);
		
		ExcelUtil.makeStringCell(row, 0, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 1, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 2, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 3, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 4, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 5, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 6, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 7, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 8, "", styles, "cell");
		ExcelUtil.makeFormulaCell(row, 9, "sum(J"+(cRow+1)+":J"+lastRow+")", styles, "cellRightBold");
		ExcelUtil.makeStringCell(row, 10, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 11, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 12, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 13, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 14, "", styles, "cell");
		ExcelUtil.makeStringCell(row, 15, "", styles, "cell");
		ExcelUtil.makeFormulaCell(row, 16, "sum(Q"+(cRow+1)+":Q"+lastRow+")", styles, "cellRightBold");
		
		ExcelUtil.makeFormulaCell(row, 17, "sum(R"+(cRow+1)+":R"+lastRow+")", styles, "cellRed");
		ExcelUtil.makeStringCell(row, 18, "", styles, "cell");
		row.setHeightInPoints(25);
		
		sheet.createFreezePane(0, 5);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Receipt maintenance Report " + 
				DateUtil.getDayOfMonth(from) + "." + (DateUtil.getMonth(from)+1) + "." + DateUtil.getYear(from) + "-" + 
				DateUtil.getDayOfMonth(to) + "." + (DateUtil.getMonth(to)+1) + "." + DateUtil.getYear(to) + ".xls\"");
		
		try {
			wb.write(RESPONSE().getOutputStream());
			this.addLoggingDetail(BaseAction.REPORT, "logging.maintenance.report.monthly", from, to);
		} catch (IOException e) {
			e.printStackTrace();
			return NONE;
		}
		return NONE;
	}
	
	/**
	 *  generate the cells for students list 
	 */
	private int generateCells(int cRow, List<ReceiptMaintenanceMonthlyBean> details, HSSFSheet sheet, Map<String, CellStyle> styles, int numberOfCols) {
		int count = 1;
		
		for (ReceiptMaintenanceMonthlyBean each : details) {
			HSSFRow row = sheet.createRow(cRow++);
			String datePattern = "MM/dd/yyyy";
			
			ExcelUtil.makeNumericCell(row, 0, count++, styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 1, DateUtil.toString(each.getMaintenanceDate(), datePattern), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 2, each.getLastName(), styles, "cell");
			ExcelUtil.makeStringCell(row, 3, each.getFirstName(), styles, "cell");
			ExcelUtil.makeStringCell(row, 4, DateUtil.toString(each.getDob(), datePattern), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 5, each.getPhone(), styles, "cell");
			ExcelUtil.makeStringCell(row, 6, each.getOfferingName(), styles, "cell");
			ExcelUtil.makeStringCell(row, 7, each.getReceiptNumber(), styles, "cell");
			
			ExcelUtil.makeStringCell(row, 8, each.getMaintenanceNumber(), styles, "cellGreen");
			ExcelUtil.makeNumericCell(row, 9, each.getMaintenanceFee().intValue(), styles, "cellMoney");
			ExcelUtil.makeStringCell(row, 10, DateUtil.toString(each.getMaintenanceDate(), datePattern), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 11, "", styles, "cell");
			ExcelUtil.makeStringCell(row, 12, each.getReason(), styles, "cell");
			
			ExcelUtil.makeStringCell(row, 13, each.getNewOfferingName(), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 14, DateUtil.toString(each.getNewOfferingStartDate(), datePattern), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 15, each.getNewReceiptNumber(), styles, "cellCenter");
			if (each.getNewOfferingName() != null)
				ExcelUtil.makeNumericCell(row, 16, each.getMaintenanceFee().intValue(), styles, "cellMoney");
			else ExcelUtil.makeNumericCell(row, 16, 0, styles, "cellMoney");
			ExcelUtil.makeFormulaCell(row, 17, "J"+cRow + "-Q" + cRow, styles, "cellMoney");
			ExcelUtil.makeStringCell(row, 18, "", styles, "cell");
			
			row.setHeightInPoints(25);
		}
		return cRow;
	}
	
	/**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)13);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        
        Font subtitleFont = wb.createFont();
        subtitleFont.setFontHeightInPoints((short)12);
        subtitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subtitleFont);
        styles.put("subtitle", style);

        Font cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        styles.put("cell", style);

        cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        DataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellMoney", style);
        
        Font cCellFont = wb.createFont();
        cCellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cCellFont);
        styles.put("cellCenter", style);
        
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cCellFont);
        styles.put("cellRight", style);
        
        Font bold = wb.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
        bold.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(bold);
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellRightBold", style);
        
        cCellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(cCellFont);
        styles.put("cellGreen", style);
        
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(bold);
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellRed", style);
        
        return styles;
    }

	public Long getReceiptId() {
		return receiptId;
	}

	public void setReceiptId(Long receiptId) {
		this.receiptId = receiptId;
	}

	public String getDate() {
		return date;
	}

	public void setDate(String date) {
		this.date = date;
	}

	public Double getFee() {
		return fee;
	}

	public void setFee(Double fee) {
		this.fee = fee;
	}

	public String getReason() {
		return reason;
	}

	public void setReason(String reason) {
		this.reason = reason;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getNumber() {
		return number;
	}

	public void setNumber(String number) {
		this.number = number;
	}
	
	public int getMonth() {
		return month;
	}

	public void setMonth(int month) {
		this.month = month;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}

	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}
}